// Ofri Keidar 302933981 group 05

package model.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import model.apartment.Apartment;
import model.apartment.ApartmentFactory;
import model.apartment.Flat;
import model.apartment.GardenApartment;
import model.apartment.Penthouse;
import model.building.Building;

/**
 * Class implements the database using MySQL
 */
public class ApartmentsDB implements Database {
	
	// connection information
	private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	private static final String DB_URL = "jdbc:mysql://localhost/";
	private static final String SERVER_NAME = "localhost";
	private static final String DB_NAME = "apartments_database";
	private static final String DB_USER = "root";
	private static final String DB_PWD = "";
	
	// database table names
	private static final String BUILDINGS_TABLE = "Buildings";
	private static final String APARTMENTS_TABLE = "Apartments";
	
	// column names in buildings table
	private static final String ID_COL = "id";
	private static final String ST_NAME_COL = "street_name";
	private static final String BLD_NUMBER_COL = "number";
	
	// column names in apartments table
	private static final String TYPE_COL = "type";
	private static final String APARTMENT_NUMBER_COL = "apartment_number";
	private static final String FLOOR_COL = "floor";
	private static final String ROOM_NUMBER_COL = "room_number";
	private static final String SIZE_COL = "size";
	private static final String EXTRA_COL = "extra_space";
	private static final String RESIDENT_NAME_COL = "resident_name";
	private static final String ADDRESS_COL = "address";
	private static final String SEP_ENTRANCE_COL = "separated_entrance";
	private static final String BLD_ID_COL = "bld_id";
		
	private static final String FLAT_VAL = "Flat"; // value of Flat type 
	private static final String PENTHOUSE_VAL = "Penthouse"; // value of Penthouse type
	private static final String GARDEN_APARTMENT_VAL = "Garden Apartment"; // value of Garden Apartment type
	private static final String SORT_CRITERIA = "("+SIZE_COL+ "+" +EXTRA_COL+")"; // criteria to sort apartments retrieved from database

	private MysqlDataSource ds; // source for data
	private ApartmentFactory factory; // factory to create apartments
	
	/**
	 * Constructor- if not exist in server, creates database and tables
	 */
	public ApartmentsDB() throws DBException {
		
		// connection to server
		Connection connection = null;
		
		try {
			
			// register JDBC driver
			Class.forName(JDBC_DRIVER);

			// open a connection
			connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PWD);
			
			// create the database
			Statement statement = connection.createStatement();
			statement.executeUpdate("CREATE DATABASE IF NOT EXISTS " + DB_NAME);
			
			// allocate a new data source
			ds = new MysqlConnectionPoolDataSource();
			
	    	// set server and database        	
	    	ds.setServerName(SERVER_NAME);
	    	ds.setDatabaseName(DB_NAME);
	    	
	    	// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			statement = connection.createStatement();
			
			// command to create buildings table			
			String command = String
					.format("CREATE TABLE IF NOT EXISTS `%s` (`%s` INT NOT NULL, `%s` VARCHAR(30) NOT NULL, `%s` INT NOT NULL, PRIMARY KEY (`%s`));",
							BUILDINGS_TABLE, ID_COL, ST_NAME_COL,
							BLD_NUMBER_COL, ID_COL);
			
			// create the table
			statement.executeUpdate(command);
			
			// command to create apartments table
			command = String
					.format("CREATE TABLE IF NOT EXISTS `%s` (`%s` VARCHAR(20) NOT NULL, `%s` INT NOT NULL, `%s` INT NOT NULL, `%s` DOUBLE NOT NULL, `%s` DOUBLE NOT NULL, `%s` DOUBLE NOT NULL, `%s` VARCHAR(30) NOT NULL, `%s` VARCHAR(40) NOT NULL, `%s` BOOLEAN, `%s` INT NOT NULL",
							APARTMENTS_TABLE, TYPE_COL, APARTMENT_NUMBER_COL,
							FLOOR_COL, ROOM_NUMBER_COL, SIZE_COL, EXTRA_COL,
							RESIDENT_NAME_COL, ADDRESS_COL, SEP_ENTRANCE_COL,
							BLD_ID_COL)
					+ String.format(
							", PRIMARY KEY (`%s`, `%s`), FOREIGN KEY (`%s`) REFERENCES `%s` (`%s`))",
							BLD_ID_COL, APARTMENT_NUMBER_COL, BLD_ID_COL,
							BUILDINGS_TABLE, ID_COL);
			
			// create the table
			statement.executeUpdate(command);

		} catch (ClassNotFoundException e) {

			// database error			
			throw new DBException("Could not initialize system- database error!");
			
		} catch (SQLException e) {
			
			// database error			
			throw new DBException("Could not initialize system- database error!");

		} finally { // close connection			
			try {
				connection.close();
			} catch (SQLException e) {
				
				// database error
				throw new DBException("Could not initialize system- database error!");
			}
		}

    	// allocate a new apartment factory
    	factory = new ApartmentFactory();
	}
	
	/**
	 * Sends database SQL query to retrieve all vacant apartments.
	 * Apartments are sorted by total area in ascending order.
	 * Throws database exception there was a problem with the database connection  
	 */
	@Override 
	public List<Apartment> getVacantApartments() throws DBException {
		
		// create query string
		String query = String.format(
				"SELECT * FROM %s WHERE %s = '' ORDER BY %s", APARTMENTS_TABLE,
				RESIDENT_NAME_COL, SORT_CRITERIA);
		
		// return apartments
		return retrieveApartments(query);		
	}
	
	/**
	 * Sends database SQL query to retrieve all vacant apartments of given type.
	 * Apartments are sorted by total area in ascending order.
	 * Throws database exception there was a problem with the database connection
	 */
	@Override
	public List<Apartment> getVacantApartments(String type) throws DBException {

		// create query string
		String query = String.format(
				"SELECT * FROM %s WHERE %s = '%s' AND %s = '' ORDER BY %s",
				APARTMENTS_TABLE, TYPE_COL, type, RESIDENT_NAME_COL,
				SORT_CRITERIA);

		// execute query
		return retrieveApartments(query);
	}

	/**
	 * Sends database SQL query to retrieve all vacant apartments of given type with
	 * specified number of rooms.
	 * Apartments are sorted by total area in ascending order.
	 * Throws database exception there was a problem with the database connection
	 */
	@Override
	public List<Apartment> getVacantApartments(String type, double roomNum)
			throws DBException {

		// create query string
		String query = String
				.format("SELECT * FROM %s WHERE %s = '%s' AND %s = '%s' AND %s = ''  ORDER BY %s",
						APARTMENTS_TABLE, TYPE_COL, type, ROOM_NUMBER_COL,
						roomNum, RESIDENT_NAME_COL, SORT_CRITERIA);
		
		// return apartments
		return retrieveApartments(query);
	}
	
	/**
	 * Sends database SQL query to retrieve all vacant apartments assigned to building
	 * with given ID.
	 * Apartments are sorted by total area in ascending order.
	 * Throws database exception if could not communicate with database
	 */
	@Override
	public List<Apartment> getVacantApartments(int buildingID)
			throws DBException {
		
		// create query string
		String query = String.format(
				"SELECT * FROM %s WHERE %s = '%s' AND %s = ''  ORDER BY %s",
				APARTMENTS_TABLE, BLD_ID_COL, buildingID, RESIDENT_NAME_COL,
				SORT_CRITERIA);
		
		// execute query
		return retrieveApartments(query);
	}
	
	/**
	 * Sends database SQL query to retrieve all vacant apartments of given type,
	 * with specified number of rooms assigned to building with given ID.
	 * Apartments are sorted by total area in ascending order.
	 * Throws database exception there was a problem with the database connection
	 */
	@Override
	public List<Apartment> getVacantApartments(String type, double roomNum,
			int buildingID) throws DBException {

		// create query string
		String query = String
				.format("SELECT * FROM %s WHERE %s = '%s' AND %s = '%s' AND %s = '%s' AND %s = '' ORDER BY %s",
						APARTMENTS_TABLE, BLD_ID_COL, buildingID, TYPE_COL,
						type, ROOM_NUMBER_COL, roomNum, RESIDENT_NAME_COL,
						SORT_CRITERIA);
		
		// execute query
		return retrieveApartments(query);
	}

	/**
	 * Sends database SQL query to retrieve all apartments of given type.
	 * Apartments are sorted by total area in ascending order.
	 * Throws database exception there was a problem with the database connection
	 */
	@Override 
	public List<Apartment> getApartments(String type) throws DBException {
		
		// create query string
		String query = String.format(
				"SELECT * FROM %s WHERE %s = '%s' ORDER BY %s",
				APARTMENTS_TABLE, TYPE_COL, type, SORT_CRITERIA);
		
		// execute query
		return retrieveApartments(query);
	}
	
	/**
	 * Sends database SQL query to retrieve all apartments of given type
	 * assigned to building with given ID.
	 * Apartments are sorted by total area in ascending order.
	 * Throws database exception there was a problem with the database connection
	 */
	@Override
	public List<Apartment> getApartments(String type, int buildingID)
			throws DBException {
		
		// create query
		String query = String.format(
				"SELECT * FROM %s WHERE %s = '%s' AND %s = '%s' ORDER BY %s",
				APARTMENTS_TABLE, BLD_ID_COL, buildingID, TYPE_COL, type,
				SORT_CRITERIA);
		
		// execute query
		return retrieveApartments(query);
	}
	
	/**
	 * Sends database SQL query to retrieve building with given address.
	 * Throws database exception there was a problem with the database connection
	 */
	@Override
	public Building getBuildingByAddress(String stName, int bldNumber)
			throws DBException {

		// initialize building found in database
		Building buildingFound = null;
		
		// connection to database
		Connection connection = null;
		
		try {
		
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();
			
			// create query string
			String query = String.format(
					"SELECT * FROM %s WHERE %s = '%s' AND %s = '%s'",
					BUILDINGS_TABLE, ST_NAME_COL, stName, BLD_NUMBER_COL,
					bldNumber); 
					
			// execute query
			ResultSet resultSet = statement.executeQuery(query);
			
			// check if such building exists
			if (!resultSet.next()) {
				
				// no such building in system
				return null;
			}
			
			// extract building id
			int bldID = resultSet.getInt(ID_COL);
			
			// create query string
			query = String.format("SELECT * FROM %s WHERE %s = '%s'",
					APARTMENTS_TABLE, BLD_ID_COL, bldID);
			
			// execute query
			resultSet = statement.executeQuery(query);
			
			// get selected building's apartments
			List<Apartment> apartments = createApartments(resultSet);
			
			// store the apartments in an array
			Apartment[] apartmentsArr = new Apartment[apartments.size()];
			apartments.toArray(apartmentsArr);
			
			// create the wanted building
			buildingFound = new Building(stName, bldNumber, apartmentsArr, bldID);
			
		} catch (SQLException e) {
			
			// database error
			throw new DBException("Action could not be done- database error!");
			
		} finally {
			
			try {
				
				// close connection
				connection.close();
				
			} catch (SQLException e) {				
				// database error
				throw new DBException("Action could not be done- database error!");
			}
		}
		
		// return building found in database
		return buildingFound;
	}

	/**
	 * Send SQL query to database to add given building.
	 * If there is already another building in the database with given building's id,
	 * building is not added and False is returned.
	 * Otherwise, adds given building and its apartments to the database.
	 * Throws database exception there was a problem with the database connection
	 */
	@Override
	public boolean addBuilding(Building buildingToAdd) throws DBException {
		
		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();
			
			// check if database holds a building with such id
			
			// create query string
			String query = String.format("SELECT * FROM %s WHERE %s = '%s'",
					BUILDINGS_TABLE, ID_COL, buildingToAdd.getID());
			
			// execute query
			ResultSet resultSet = statement.executeQuery(query);
			
			// check if building exists				
			if(resultSet.next()) {
				
				// building already exists
				return false;
			}

			// add the building			
			
			// create query string
			query = String.format(
					"INSERT INTO %s (%s, %s, %s) VALUES ('%s', '%s', '%s')",
					BUILDINGS_TABLE, ID_COL, ST_NAME_COL, BLD_NUMBER_COL,
					buildingToAdd.getID(), buildingToAdd.getStreetName(),
					buildingToAdd.getBuildingNumber());
			
			// execute query
			statement.execute(query);
			
			// add the building's apartments
			Apartment[] apartments = buildingToAdd.getApartments();
			int numOfApartments = apartments.length;
			for (int i = 0 ; i < numOfApartments ; ++i) {
				
				// check if separate entrance data is relevant for current apartment
				int hasSepEntrance = 0;
				if (apartments[i] instanceof GardenApartment) {
					if ( ((GardenApartment)apartments[i]).hasSepEntrance() ) {
						hasSepEntrance = 1;
					}
				}
				
				// create query string
				query = String
						.format("INSERT INTO %s (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
								APARTMENTS_TABLE, TYPE_COL, APARTMENT_NUMBER_COL, FLOOR_COL,
								ROOM_NUMBER_COL, SIZE_COL, EXTRA_COL,
								RESIDENT_NAME_COL, ADDRESS_COL,
								SEP_ENTRANCE_COL, BLD_ID_COL,
								getApartmentType(apartments[i]), (i+1), apartments[i].getFloor(),
								apartments[i].getRoomNum(), apartments[i].getSize(),
								(apartments[i].totalArea() - apartments[i].getSize()), apartments[i].getName(),
								buildingToAdd.getBuildingAddress(),
								hasSepEntrance, buildingToAdd.getID());
				
				// execute query
				statement.execute(query);
			}
			
			// building and its apartments were added successfully
			return true;
		
		} catch (SQLException e) {
			
			// database error
			throw new DBException("Could not add building- database error!");
			
		} finally {
			
			try {
				
				// close connection
				connection.close();
				
			} catch (SQLException e) {
				// database error
				throw new DBException("Could not add building- database error!");
			}
		}
	}

	/**
	 * Sends database SQL query to update resident name of apartment with given details.
	 * If there is no such apartment, returns False.
	 * Otherwise, updates resident name and returns True.
	 * Throws database exception there was a problem with the database connection
	 */
	@Override
	public boolean updateResident(String newResName, int buildingID,
			int apartmentNum) throws DBException {
		
		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();
			
			// check if there is an apartment with given details
			String query = String
					.format("SELECT * FROM %s WHERE %s = '%s' AND %s = '%s' AND %s = ''",
							APARTMENTS_TABLE, BLD_ID_COL, buildingID,
							APARTMENT_NUMBER_COL, apartmentNum,
							RESIDENT_NAME_COL);
			
			// execute query
			ResultSet resultSet = statement.executeQuery(query);
			
			// check if result set is not empty
			if (!resultSet.next()) {
				
				// no apartment with given details
				return false;
			}
			
			// create query string to update resident name
			query = String.format(
					"UPDATE %s SET %s = '%s' WHERE %s = '%s' AND %s = '%s'",
					APARTMENTS_TABLE, RESIDENT_NAME_COL, newResName,
					BLD_ID_COL, buildingID, APARTMENT_NUMBER_COL, apartmentNum);
			
			// execute query
			statement.execute(query);
			return true;
			
		} catch (SQLException e) {
			
			// database error
			throw new DBException("Could not update resident name- database error!");
			
		} finally {
			
			try {
				
				// close connection
				connection.close();
				
			} catch (SQLException e) {
				// database error				
				throw new DBException("Could not update resident name- database error!");
			}
		}
	}
	
	/**
	 * Sends SQL query to count number of rows in building table, and returns calculated number.
	 * Throws database exception there was a problem with the database connection
	 */
	@Override
	public int countBuildings() throws DBException {

		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();

			// create query string
			String query = String.format("SELECT COUNT(%s) FROM %s", ID_COL,
					BUILDINGS_TABLE);
			
			// execute query
			ResultSet resultSet = statement.executeQuery(query);
			
			// move to first element on the result set
			resultSet.next();

			// return number of rows
			return resultSet.getInt(1);
			
			
		} catch (SQLException e) {
			
			// database error
			throw new DBException("Action could not be done- database error!");
			
		} finally {
			
			try {
				
				// close connection
				connection.close();
				
			} catch (SQLException e) {				
				// database error
				throw new DBException("Action could not be done- database error!");
			}
		}
	}

	/**
	 * Executes queries regarding apartments data- gets a query and returns its result.
	 * Data is returned as a list of the relevant apartments
	 * @param query- string holding query to database
	 * @return apartments retrieved from given query
	 * @throws DBException- throws database exception there was a problem with the database connection
	 */
	private List<Apartment> retrieveApartments(String query) throws DBException {

		// initialize list to hold all apartments found
		List<Apartment> vacantApartments = new LinkedList<Apartment>();
		
		// connection to database
		Connection connection = null;
		
		try {
			
			// connect to database
			connection = ds.getConnection(DB_USER, DB_PWD);
			Statement statement = connection.createStatement();
			
			// execute query
			ResultSet resultSet = statement.executeQuery(query);
	
			// get all vacant apartments found
			resultSet.beforeFirst(); // initialize position on result set
			vacantApartments.addAll(createApartments(resultSet));
			
		} catch (SQLException e) {
			
			// database error
			throw new DBException("Could not retrieve apatments- database error!");
			
		} finally {
			
			try {
				
				// close connection
				connection.close();
				
			} catch (SQLException e) {				
				// database error
				throw new DBException("Could not retrieve apatments- database error!");
			}
		}
		
		// return apartments found
		return vacantApartments;
	}
	
	/**
	 * Gets database query result and creates apartments according to given data 
	 * @param resultSet- holds database query result
	 * @return apartments matching retrieved data
	 * @throws DBException- throws database exception there was a problem with the database connection
	 */
	private List<Apartment> createApartments(ResultSet resultSet) throws DBException {

		// allocate list to hold the new apartments
		List<Apartment> apartments = new LinkedList<Apartment>();
		
		try {
			
			// scan all rows found
			while (resultSet.next()) {

				// extract current apartment's attributes
				String type = resultSet.getString(TYPE_COL);
				int floor = resultSet.getInt(FLOOR_COL);
				double roomNumber = resultSet.getDouble(ROOM_NUMBER_COL);
				double size = resultSet.getDouble(SIZE_COL);
				double extra = resultSet.getDouble(EXTRA_COL);
				String residentName = resultSet.getString(RESIDENT_NAME_COL);
				boolean separateEntrace = resultSet.getBoolean(SEP_ENTRANCE_COL);
				int apartmentNumber = resultSet.getInt(APARTMENT_NUMBER_COL);
				int bldID = resultSet.getInt(BLD_ID_COL);
				String address = resultSet.getString(ADDRESS_COL);

				// create apartment with extracted attributes
				Apartment currApartment = factory.create(type, floor,
						roomNumber, size, extra, residentName, separateEntrace,
						apartmentNumber, bldID, address);

				// add to list
				apartments.add(currApartment);
			}

			// move back to initial position in result set
			resultSet.beforeFirst();
			
		} catch (SQLException e) {
			
			// database error
			throw new DBException("Could not retrieve apatments- database error!");
		}		
		
		// return apartments found
		return apartments;
	}

	/**
	 * Gets an apartment and returns its type name.
	 * @param apartment- apartment to check
	 * @return given apartment's type name
	 */
	private String getApartmentType(Apartment apartment) {
		
		// check if a Flat was given
		if (apartment instanceof Flat) { return FLAT_VAL; }
		
		// check if a Penthouse was given
		if (apartment instanceof Penthouse) { return PENTHOUSE_VAL; }
		
		// given apartment is a Garden Apartment
		return GARDEN_APARTMENT_VAL;
	}
}